﻿using System;
using System.Diagnostics;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Reflection;
using System.Net;
using System.IO;
using System.IO.Compression;
using System.Collections.Specialized;
using System.Collections;
using System.Xml.Serialization;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using GLiteInterfaces;

namespace GeneralUtilities
{
    public enum Pars
    {
        NumeracionAutomatica = 6,
        MaximoDiasOperacion = 11,
        GananciaMinimaAceptable = 12,
        GananciaMaximaAceptable = 13,
        DiasAlertaActividades = 14,
        PesoMinimo = 19,
        PesoMaximo = 20,
        PrecioKiloMinimo = 21,
        PrecioKiloMaximo = 22,
        PrecioAlojoMinimo = 23,
        PrecioAlojoMaximo = 24,
        FechaUltimaActualizacion = 25,
        FechaUltimoUpload = 26,
    }

    public static class ConfigUtilities
    {
        public static string GetDbConnElement(string connectionStringName, string elementName)
        {
            string elementValue = string.Empty;
            string itemUpper;

            elementName = elementName.ToUpper().Trim() + "=";
            // Get the server name from the connection string
            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
            string[] split = connStr.Split(new Char[] { ';' });

            foreach (string item in split)
            {
                itemUpper = item.ToUpper().Trim();
                if (itemUpper.Contains(elementName))
                {
                    elementValue = itemUpper.Replace(elementName, string.Empty);
                    break;
                }
            }

            return elementValue;

        }
    }

    public static class UtilityDB
    {
        static NameValueCollection PrimaryKeys = new NameValueCollection();
        static NameValueCollection LookupDescripcion = new NameValueCollection();
        static string connStringItem = "GanagroDatosLite";
        static string _user = string.Empty;
        static string _clave = string.Empty;

        static string GetConnectionString()
        {
            string connString = string.Format("data source={0};initial catalog={1};persist security info=True;user id={2};password={3}",
                GetServerName(),
                GetDbName(),
                GetUserName(),
                _clave);
            return connString;
        }

        public static void SetUser(string p)
        {
            _user = p;
        }

        public static void SetPwd(string clave)
        {
            _clave = clave;
        }

        public static string GetPwd()
        {
            return _clave;
        }

        public static string GetServerName()
        {
            string serverName = ConfigurationManager.AppSettings["ServerName"];
            if (serverName == null)
            { serverName = @"nt6k548vy8.database.windows.net"; }
            return serverName;
        }

        public static string GetUserName()
        {
            string userName = ConfigurationManager.AppSettings["UserName"];
            if (userName == null)
            { userName = @"pedro"; }
            return userName;
        }

        public static string GetDbName()
        {
            string dbName = ConfigurationManager.AppSettings["DbName"];
            if (dbName == null)
            { dbName = @"GLPROD"; }
            return dbName;
        }

        public static void SetConnectionStringItem(string dbstrItem)
        {
            connStringItem = dbstrItem;
        }

        public static Int32 GetNextId(string tableName)
        {
            byte retval = 0;

            loadPks();
            string[] columnNames = PrimaryKeys.GetValues(tableName);
            string columnName = columnNames[0];
            using (SqlConnection conn = new SqlConnection(GetConnectionString()))
            {

                SqlCommand cmd = new SqlCommand(string.Format("SELECT max({1}) FROM {0}", tableName, columnName), conn);

                conn.Open();


                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                    retval = Convert.ToByte(dr.GetInt16(0));

                conn.Close();

            }

            return retval;

        }

        public static String GetGananciaVida(string Codigo)
        {
            return GetVal(string.Format("select round(GananciaDia,0) from RepGananciaVida where Codigo='{0}'", Codigo));
        }

        public static String GetVal(string SQL)
        {
            string retval = string.Empty;

            using (SqlConnection conn = new SqlConnection(UtilityDB.GetConnectionString()))
            {

                conn.Open();

                using (SqlCommand cmd = new SqlCommand(SQL, conn))
                {
                    object xsc = cmd.ExecuteScalar();
                    if (xsc != null)
                        retval = xsc.ToString();

                    conn.Close();
                }
            }

            return retval;

        }

        public static bool ExecuteCommand(string SQL)
        {
            return ExecuteCommand(SQL, string.Empty);
        }

        public static bool ExecuteCommand(string SQL, string defaultDB)
        {
            bool retval = false;

            string connString = UtilityDB.GetConnectionString();
            if (defaultDB != string.Empty)
            {
                string DatabaseName = ConfigUtilities.GetDbConnElement(connStringItem, "DATABASE");
                connString = connString.Replace(DatabaseName, "master");
            }

            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL, conn);
                bool success = cmd.ExecuteNonQuery() > 0;
                conn.Close();

            }

            return retval;

        }

        public static String GetVal(string SQL, string targetField)
        {
            string retval = string.Empty;

            using (SqlConnection conn = new SqlConnection(UtilityDB.GetConnectionString()))
            {

                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL, conn);
                SqlDataReader dr = cmd.ExecuteReader();

                if (dr.Read())
                    retval = dr[targetField].ToString();

                conn.Close();

            }

            return retval;

        }

        public static List<string> GetVals(string SQL)
        {
            List<string> rv = new List<string>();
            using (SqlConnection conn = new SqlConnection(UtilityDB.GetConnectionString()))
            {

                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL, conn);
                SqlDataReader dr = cmd.ExecuteReader();

                if (dr.Read())
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        rv.Add(dr[i].ToString());
                    }

                conn.Close();


            }

            return rv;

        }

        //public static bool exists(string tablename, string primaryKey)
        //{
        //    string pkColName = GetPkColName(tablename);
        //    string sql = string.Format("SELECT {0} FROM {1} WHERE {2} = '{3}' ", pkColName, tablename, pkColName, primaryKey);
        //    return (GetVal(sql) != String.Empty);
        //}

        public static bool Exists(string sTable, string sWhere)
        {
            bool retVal = false;
            string sqlStatement = null;
            using (SqlConnection conn = new SqlConnection(UtilityDB.GetConnectionString()))
            {
                conn.Open();
                sqlStatement = string.Format("Select * from {0} WHERE {1}", sTable, sWhere);
                string SQL = string.Format("if exists({0}) select 1 as extant else select 0 as extant", sqlStatement);
                SqlCommand cmd = new SqlCommand(SQL, conn);
                cmd.CommandType = CommandType.Text;
                object obj = cmd.ExecuteScalar();
                retVal = (obj != null) ? (Convert.ToInt16(obj) > 0) : false;
            }
            return retVal;
        }

        private static void loadDescs()
        {
            if (LookupDescripcion.Count == 0)
            {
                LookupDescripcion.Add("COLORES", "Descripcion");
                LookupDescripcion.Add("Estados", "Descripcion");
                LookupDescripcion.Add("LOTES", "Descripcion");
                LookupDescripcion.Add("Fincas", "Nombre");
                LookupDescripcion.Add("Razas", "Descripcion");
                LookupDescripcion.Add("Tipo_Vacuna", "Descripcion");
                LookupDescripcion.Add("Tipos_Actividades", "Descripcion");
                LookupDescripcion.Add("Tipos_Materiales", "Descripcion");
                LookupDescripcion.Add("Tipos_Mvto_Semen", "Descripcion");
                LookupDescripcion.Add("Tipos_Operacion", "Descripcion");
            }
        }

        private static void loadPks()
        {
            if (PrimaryKeys.Count == 0)
            {
                PrimaryKeys.Add("Backups", "Fecha");
                PrimaryKeys.Add("COLORES", "ID");
                PrimaryKeys.Add("Estados", "ID");
                PrimaryKeys.Add("Lotes", "ID");
                PrimaryKeys.Add("Fincas", "Finca");
                PrimaryKeys.Add("Ganado", "Codigo");
                PrimaryKeys.Add("Hisreversos", "Consecutivo");
                PrimaryKeys.Add("Parametros", "Item");
                PrimaryKeys.Add("Razas", "ID");
                PrimaryKeys.Add("Reversos", "Consecutivo");
                PrimaryKeys.Add("Tipos_Actividades", "Nroactividad");
                PrimaryKeys.Add("Tipos_Operacion", "Operacion");
            }
        }

        public static string GetPkColName(string tablename)
        {
            loadPks();
            string[] columnNames = PrimaryKeys.GetValues(tablename);
            if (columnNames.Length > 0)
                return columnNames[0];
            else
                return string.Empty;
        }

        private static DataTable _colores;
        public static  DataTable Loadcolores()
        {
            if(_colores == null)
                _colores = UtilityDB.LoadData("COLORES", "DESCRIPCION");

            return _colores;
        }

        private static DataTable _estados;
        public static DataTable Loadestados()
        {
            if(_estados == null)
                _estados = UtilityDB.LoadData("estados", "DESCRIPCION");

            return _estados;
        }

        private static DataTable _razas;
        public static DataTable Loadrazas()
        {
            if(_razas == null)
                _razas = UtilityDB.LoadData("razas", "DESCRIPCION");

            return _razas;
        }

        private static DataTable _lotes;
        public static DataTable Loadlotes()
        {
            if(_lotes == null)
                _lotes = UtilityDB.LoadData("lotes", "DESCRIPCION");

            return _lotes;
        }

        public static DataTable LoadData(string tableName, string keyValue)
        {
            string columnName = GetPkColName(tableName);
            SqlDataAdapter da = new SqlDataAdapter();
            using (SqlConnection conn = new SqlConnection(UtilityDB.GetConnectionString()))
            {
                conn.Open();
                string sql = string.Format("SELECT {0},{1} FROM {2} ORDER BY 2", columnName, keyValue, tableName);
                da.SelectCommand = new SqlCommand(sql, conn);

                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        public static DataTable LoadComentarios(short tOp)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            using (SqlConnection conn = new SqlConnection(UtilityDB.GetConnectionString()))
            {
                conn.Open();
                string sql = string.Format("SELECT NCOMENTARIO,DESCRIPCION FROM COMENTARIOS WHERE OPERACION = {0} ORDER BY 2", (short)tOp);
                da.SelectCommand = new SqlCommand(sql, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        public static DataTable PopulateDTFromSQL(string strSQL)
        {
            int nColumns = 0;
            return PopulateDTFromSQL(strSQL, ref nColumns);
        }

        public static DataTable PopulateDTFromSQL(string strSQL, ref int nColumns)
        {

            SqlDataAdapter da = new SqlDataAdapter();
            using (SqlConnection conn = new SqlConnection(UtilityDB.GetConnectionString()))
            {
                da.SelectCommand = new SqlCommand(strSQL, conn);

                DataTable dt = new DataTable();
                dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
                da.Fill(dt);
                nColumns = dt.Columns.Count;

                return dt;
            }


        }

    }

    public static class IOUtilities
    {
        #region File I/O

        public static string Compress(string filename)
        {
            FileInfo fi = new FileInfo(filename);
            string compressedFileName = string.Empty;

            // Get the stream of the source file.
            using (FileStream inFile = fi.OpenRead())
            {
                // Prevent compressing hidden and already compressed files.
                if ((File.GetAttributes(fi.FullName) & FileAttributes.Hidden)
                        != FileAttributes.Hidden & fi.Extension != ".gz")
                {
                    compressedFileName = fi.FullName.Replace(".bak", string.Empty) + ".gz";
                    // Create the compressed file.
                    using (FileStream outFile = File.Create(compressedFileName))
                    {
                        using (GZipStream Compress = new GZipStream(outFile,
                                CompressionMode.Compress))
                        {
                            // Copy the source file into the compression stream.
                            byte[] buffer = new byte[4096];
                            int numRead;
                            while ((numRead = inFile.Read(buffer, 0, buffer.Length)) != 0)
                            {
                                Compress.Write(buffer, 0, numRead);
                            }
                        }
                    }
                }
            }

            return compressedFileName;
        }

        public static bool Decompress(string filename, string targetFileName)
        {
            bool success = false;
            try
            {
                FileInfo fi = new FileInfo(filename);
                // Get the stream of the source file.
                using (FileStream inFile = fi.OpenRead())
                {
                    // Get original file extension, for example "doc" from report.doc.gz.
                    string curFile = fi.FullName;

                    //Create the decompressed file.
                    using (FileStream outFile = File.Create(targetFileName))
                    {
                        using (GZipStream Decompress = new GZipStream(inFile,
                                CompressionMode.Decompress))
                        {
                            //Copy the decompression stream into the output file.
                            byte[] buffer = new byte[4096];
                            int numRead;
                            while ((numRead = Decompress.Read(buffer, 0, buffer.Length)) != 0)
                            {
                                outFile.Write(buffer, 0, numRead);
                            }
                        }
                    }

                    success = true;
                }
            }
            catch
            {
                success = false;
            }

            return success;
        }

        public static void LogitToDisk(string filename, string message, bool RecrearSinTimestamp)
        {
            if (RecrearSinTimestamp)
            { File.Delete(filename); }
            try
            {
                StreamWriter tw;

                if (!File.Exists(filename))
                { tw = File.CreateText(filename); }
                else
                { tw = new StreamWriter(filename, true); }

                tw.WriteLine(message);
                tw.Flush();
                tw.Close();

            }
            catch (Exception ex)
            {
                EventLog.WriteEntry("GanagroLite_LogitToDisk", ex.Message);
            }

        }

        public static void LogitToDisk(string filename, string message)
        {
            try
            {

                StreamWriter tw;

                if (!File.Exists(filename))
                { tw = File.CreateText(filename); }
                else
                { tw = new StreamWriter(filename, true); }

                message = DateTime.Now.ToString() + " " + message;
                tw.WriteLine(message);
                tw.Flush();
                tw.Close();

            }
            catch (Exception ex)
            {
                EventLog.WriteEntry("GanagroLite_LogitToDisk", ex.Message);
            }
        }

        #endregion

    }

    public static class BackupUtilities
    {
        #region Database Backup / restore

        public static DateTime BdUploaded { get; set; }

        public static string BackupDB(string DbConfigName, IOperacionView iov, string dir)
        {
            return BackupDB(DbConfigName, dir, iov);
        }

        private static string BackupDB(string DbConfigName, string dir, IOperacionView iov)
        {
            iov.SetUIBusyStatus();
            iov.status2 = "Creando respaldo de la base de datos...";

            string backupName = BackupDatabase(dir, DbConfigName);
           
            return backupName;

        }

        public static string GetDirectorioCopias()
        {
            string dir = ConfigurationManager.AppSettings["CopiasBDDir"];

            if (dir == null)
            { dir = @"C:\CopiasBD"; }
            return dir;
        }


        public static string GetDirectorioNube()
        {
            string dir = ConfigurationManager.AppSettings["CopiasNube"];

            if (dir == null)
            { dir = @"C:\users\pedro\Google Drive"; }
            return dir;
        }

        public static void BackupDatabase(string serverName, string databaseName, string backupFileName)
        {
            BackupDeviceItem bdi = new BackupDeviceItem(backupFileName, DeviceType.File);
            Backup bu = new Backup();
            bu.Database = databaseName;
            bu.Devices.Add(bdi);
            bu.Initialize = true;
            // add percent complete and complete event handlers
            //            bu.PercentComplete += new PercentCompleteEventHandler(Backup_PercentComplete);
            //            bu.Complete += new ServerMessageEventHandler(Backup_Complete);
            Server server = new Server(serverName);
            bu.SqlBackup(server);
        }

        public static void RestaurarBD(string DbConfigName, IOperacionView iov)
        {
            // TODO: chequear con usuario
            FtpHelper fH = new FtpHelper();
            UtilityDB.SetConnectionStringItem(DbConfigName);
            string ServerName = ConfigUtilities.GetDbConnElement(DbConfigName, "SERVER");
            string DatabaseName = ConfigUtilities.GetDbConnElement(DbConfigName, "DATABASE");

            //            string DatabaseFileName =  string.Format(@"{0}\{1}", Environment.CurrentDirectory, DatabaseName + ".gz");
            string DatabaseFileName = DatabaseName + ".gz";
            iov.status2 = "Obteniendo archivo desde sitio FTP";
            if (fH.Download(Environment.CurrentDirectory, DatabaseFileName, "Databases"))
            {
                string backupFilename = string.Format(@"{0}\{1}", Environment.CurrentDirectory, DatabaseFileName.Replace(".gz", ".bak"));
                iov.status2 = "Descomprimiendo Archivo de backup";
                if (IOUtilities.Decompress(DatabaseFileName, backupFilename))
                {
                    iov.status2 = "Restaurando la base de datos.";
                    BackupUtilities.RestoreDatabase(ServerName, DatabaseName, backupFilename, iov);
                    iov.status2 = "Base de datos lista para usar.";
                }
            }
        }

        public static IOperacionView _iov;
        public static bool RestoreDatabase(string serverName, string databaseName, string backupFileName, IOperacionView IOV)
        {
            Boolean success = false;
            try
            {
                UtilityDB.ExecuteCommand(string.Format("Alter database {0} set single_user with rollback immediate", databaseName), "master");
                UtilityDB.ExecuteCommand(string.Format("DROP database {0} ", databaseName), "master");

                Server svr = new Server(serverName);
                Restore res = new Restore();
                res.Database = databaseName;
                res.Action = RestoreActionType.Database;
                res.Devices.AddDevice(backupFileName, DeviceType.File);
                res.PercentCompleteNotification = 10;
                res.ReplaceDatabase = true;
                //                res.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);

                _iov = IOV;
                res.SqlRestore(svr);
                res.Complete += new ServerMessageEventHandler(res_Complete);
            }
            catch (Exception Ex)
            {
                IOV.status2 = Ex.Message;
            }
            finally
            {
                UtilityDB.ExecuteCommand(string.Format("Alter database {0} set multi_user", databaseName), "master");
            }

            return success;
        }

        static void res_Complete(object sender, ServerMessageEventArgs e)
        {
            _iov.status2 = "La base de datos ha sido restaurada";
        }

        //static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
        //{
        //    Console.WriteLine(e.Percent.ToString() + "% restored");
        //}

        /// <summary>
        /// BackupDatabase
        /// </summary>
        /// <param name="connectionStringName"></param>
        /// <returns>name of the compressed backup file: databasename.bak.gz</returns>
        public static string BackupDatabase(string backupDir, string connectionStringName)
        {
            if (!Directory.Exists(backupDir))
             { 
                 Directory.CreateDirectory(backupDir); 
             }

            string ServerName     = ConfigUtilities.GetDbConnElement(connectionStringName, "SERVER");
            string DatabaseName   = ConfigUtilities.GetDbConnElement(connectionStringName, "DATABASE");
            string BackupFileName = GetDBBackupFileName(backupDir, connectionStringName);
            BackupDatabase(ServerName, DatabaseName, BackupFileName);

            return BackupFileName;
        }

        public static string GetDBBackupFileName(string backupDir, string connectionStringName)
        {
            return string.Format("{0}\\{1}.bak", backupDir, ConfigUtilities.GetDbConnElement(connectionStringName, "DATABASE"));
        }
        #endregion
    }



    #region Network / FTP  related

    public class FtpHelper
    {

        #region properties
        private string ftpUId;

        private string ftpp;

        private string ftpServerIP;

        public string FtpServerIP
        {
            get { return ftpServerIP; }
            set { ftpServerIP = value; }
        }

        #endregion

        #region methods

        private Uri BuildServerUri(string Host, string Port, string Path)
        {
            return new Uri(String.Format("ftp://{0}:{1}/{2}", Host, Port, Path));
        }

        public bool Upload(string localfilename, string remotefilename)
        {
            return Upload(localfilename, remotefilename, string.Empty);
        }

        public bool Upload(string localfilename, string remotefilename, string remotedir)
        {
            bool success = false;
            try
            {
                if (remotedir == string.Empty)
                { remotedir = "Databases/"; }
                else
                { remotedir = remotedir + "/"; }

                FileInfo fileInf = new FileInfo(localfilename);
                string uri = "ftp://" + ftpServerIP + "/" + fileInf.Name;
                FtpWebRequest reqFTP;

                // Create FtpWebRequest object from the Uri provided
                reqFTP = (FtpWebRequest)FtpWebRequest.Create(BuildServerUri(ftpServerIP, "21", remotedir + remotefilename));

                // Provide the WebPermission Credintials
                reqFTP.Credentials = new NetworkCredential(ftpUId, ftpp);

                // By default KeepAlive is true, where the control connection is not closed
                // after a command is executed.
                reqFTP.KeepAlive = false;

                // Specify the command to be executed.
                reqFTP.Method = WebRequestMethods.Ftp.UploadFile;

                // Specify the data transfer type.
                reqFTP.UseBinary = true;

                // Notify the server about the size of the uploaded file
                reqFTP.ContentLength = fileInf.Length;

                // The buffer size is set to 2kb
                int buffLength = 2048;
                byte[] buff = new byte[buffLength];
                int contentLen;

                // Opens a file stream (System.IO.FileStream) to read the file to be uploaded
                FileStream fs = fileInf.OpenRead();

                // Stream to which the file to be upload is written
                Stream strm = reqFTP.GetRequestStream();

                // Read from the file stream 2kb at a time
                contentLen = fs.Read(buff, 0, buffLength);

                // Till Stream content ends
                while (contentLen != 0)
                {
                    // Write Content from the file stream to the FTP Upload Stream
                    strm.Write(buff, 0, contentLen);
                    contentLen = fs.Read(buff, 0, buffLength);
                }

                // Close the file stream and the Request Stream
                strm.Close();
                fs.Close();
                success = true;

                return success;
            }
            catch (Exception ex)
            {
                //EventLog.WriteEntry("GanagroLite", ex.Message);
                IOUtilities.LogitToDisk("UploadErrors.log", ex.Message);
                return success;
            }
        }

        public bool Download(string filePath, string fileName, string remoteDir)
        {
            FtpWebRequest reqFTP;
            bool success = false;
            try
            {
                //filePath = <<The full path where the file is to be created.>>, 
                //fileName = <<Name of the file to be created(Need not be the name of the file on FTP server).>>
                FileStream outputStream = new FileStream(filePath + @"\" + fileName, FileMode.Create);
                string remoteFile = string.Format("{0}/{1}", remoteDir, fileName);
                reqFTP = (FtpWebRequest)FtpWebRequest.Create(BuildServerUri(ftpServerIP, "21", remoteFile));
                reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
                reqFTP.UseBinary = true;
                reqFTP.Credentials = new NetworkCredential(ftpUId, ftpp);
                FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
                Stream ftpStream = response.GetResponseStream();
                long cl = response.ContentLength;
                int bufferSize = 2048;
                int readCount;
                byte[] buffer = new byte[bufferSize];

                readCount = ftpStream.Read(buffer, 0, bufferSize);
                while (readCount > 0)
                {
                    outputStream.Write(buffer, 0, readCount);
                    readCount = ftpStream.Read(buffer, 0, bufferSize);
                }

                ftpStream.Close();
                outputStream.Close();
                response.Close();

                success = true;
            }
            catch (Exception ex)
            {
                IOUtilities.LogitToDisk("DownloadErrors.log", ex.Message);
            }

            return success;
        }

        #endregion

        public FtpHelper()
        {
            ftpServerIP = "ftp.solucionesganaderas.com";
            ftpUId = "peyoquintero";
            ftpp = "Pquinter97";

        }

    }

    #endregion
}
